package it.trekker.db;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class ExcursionDAO {

	private static final String TAG = ExcursionDAO.class.getSimpleName();
	
	private static final String allColumns[] = new String[] {
		ExcursionTableMeta.KEY_ROWID,
		ExcursionTableMeta.KEY_NAME,
		ExcursionTableMeta.KEY_DATE, 
		ExcursionTableMeta.KEY_DISTANCE,
		ExcursionTableMeta.KEY_TOTALTITUDEDIFF,
		ExcursionTableMeta.KEY_TIME,
		ExcursionTableMeta.KEY_MEANPACE,
		ExcursionTableMeta.KEY_STEPS};
	
	private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	private static Context context = null;
	private SQLiteDatabase db = null;
	private DatabaseHelper dbHelper = null;

	public ExcursionDAO(Context context) {
		dbHelper = new DatabaseHelper(context);
	}

	public SQLiteDatabase open() {
		if(db == null || !db.isOpen()) {
			db = dbHelper.getWritableDatabase();
			db.execSQL("PRAGMA foreign_keys=ON;");
		}
		
		return db;
	}

	public void close() {
		if(db != null && db.isOpen())
			db.close();
	}
	
	public boolean isOpen() {
		return db.isOpen();
	}

	public int insertExcursion(Excursion excursion) {

		ContentValues cv = new ContentValues();

		cv.put(ExcursionTableMeta.KEY_NAME, excursion.getName());
//		cv.put(ExcursionTableMeta.KEY_DATE, "datetime()");
		cv.put(ExcursionTableMeta.KEY_STEPS, excursion.getSteps());

		return (int) db.insert(ExcursionTableMeta.DATABASE_TABLE, null, cv);
	}

	public boolean deleteExcursion(long id) {
		return (db.delete(ExcursionTableMeta.DATABASE_TABLE, ExcursionTableMeta.KEY_ROWID + "=" + id, null) > 0);
	}

	public List<Excursion> getAllExcursions() {

		List<Excursion> result = null;

		Cursor cursor = getAllExcursionsAsCursor();

		if(cursor != null) {

			result = new ArrayList<Excursion>();

			while(cursor.moveToNext()) {

				Excursion excursion = cursorToExcursion(cursor);

				result.add(excursion);
			}

			cursor.close();
		}

		return result;
	}
	
	public Cursor getAllExcursionsAsCursor() {

		Cursor cursor = db.query(ExcursionTableMeta.DATABASE_TABLE, 
				allColumns,
				null,
				null,
				null,
				null,
				null);

		return cursor;
	}

	public Excursion getExcursion(int id) {

		Excursion result = null;

		Cursor cursor = db.query(true, ExcursionTableMeta.DATABASE_TABLE, 
				allColumns,
				ExcursionTableMeta.KEY_ROWID + "=" + id,
				null,
				null,
				null,
				null,
				null);

		if (cursor != null) {
			cursor.moveToFirst();

			result = cursorToExcursion(cursor);

			cursor.close();
		}

		return result;
	}
	
	public Excursion getExcursionByName(String name) {

		Excursion result = null;

		Cursor cursor = db.query(true, ExcursionTableMeta.DATABASE_TABLE, 
				allColumns,
				ExcursionTableMeta.KEY_NAME + "=" + name,
				null,
				null,
				null,
				null,
				null);

		if (cursor != null) {
			cursor.moveToFirst();

			result = cursorToExcursion(cursor);

			cursor.close();
		}

		return result;
	}

	public boolean updateExcursion(Excursion excursion) {

		ContentValues args = new ContentValues();

		args.put(ExcursionTableMeta.KEY_NAME, excursion.getName());
		args.put(ExcursionTableMeta.KEY_STEPS, excursion.getSteps());
		args.put(ExcursionTableMeta.KEY_DISTANCE, excursion.getDistance());
		args.put(ExcursionTableMeta.KEY_MEANPACE, excursion.getMeanPace());
		args.put(ExcursionTableMeta.KEY_TOTALTITUDEDIFF, excursion.getTotAltitudeDifference());
		args.put(ExcursionTableMeta.KEY_TIME, excursion.getTime());

		return (db.update(ExcursionTableMeta.DATABASE_TABLE, args, ExcursionTableMeta.KEY_ROWID + "=" + excursion.getId(), null) > 0);
	}

	private Excursion cursorToExcursion(Cursor cursor) {

		Excursion excursion = null;

		if(cursor != null) {
			
			excursion = new Excursion();
			
			int idCol = cursor.getColumnIndexOrThrow(ExcursionTableMeta.KEY_ROWID);
			int nameCol = cursor.getColumnIndexOrThrow(ExcursionTableMeta.KEY_NAME);
			int dateCol = cursor.getColumnIndexOrThrow(ExcursionTableMeta.KEY_DATE);
			int stepsCol = cursor.getColumnIndexOrThrow(ExcursionTableMeta.KEY_STEPS);
			int distanceCol = cursor.getColumnIndexOrThrow(ExcursionTableMeta.KEY_DISTANCE);
			int meanPaceCol = cursor.getColumnIndexOrThrow(ExcursionTableMeta.KEY_MEANPACE);
			int altitudeDiffCol = cursor.getColumnIndexOrThrow(ExcursionTableMeta.KEY_TOTALTITUDEDIFF);
			int timeCol = cursor.getColumnIndexOrThrow(ExcursionTableMeta.KEY_TIME);

			excursion.setId(cursor.getInt(idCol));
			excursion.setName(cursor.getString(nameCol));
			try {
				excursion.setDate(dateFormat.parse(cursor.getString(dateCol)));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			excursion.setSteps(cursor.getLong(stepsCol));
			excursion.setDistance(cursor.getDouble(distanceCol));
			excursion.setMeanPace(cursor.getDouble(meanPaceCol));
			excursion.setTotAltitudeDifference(cursor.getDouble(altitudeDiffCol));
			excursion.setTime(cursor.getLong(timeCol));
		}

		return excursion;
	}
}
